﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

using Microsoft.Win32;
using System.IO;
using System.Text.RegularExpressions;

namespace YizitCodeCreater
{
    public partial class Form1 : Form
    {
        static string connectOracle = "";
        static string viewTable = "";
        static string root_path = System.Environment.CurrentDirectory;
        public static string tns_path = "";
        public static string config_path = root_path + @"\tnsPath.config";
        public static string save_host = "";
        public static string save_port = "";
        public static string save_name = "";
        public static string tnsText = "";
        public static string basePath = Environment.CurrentDirectory;

        //绑定到下拉空间的数据库名称
        static DataTable dtBind = new DataTable();
        public static DataTable dtTable = new DataTable();//表名，列名，类型，注释
        public static DataTable dtTableInfo = new DataTable();//表名，表注释
        public static DataTable dtTableP = new DataTable(); //,表名，主键

        public Form1()
        {
            InitializeComponent();
            BTCopy.Enabled = false;
            try
            {
                if (!Directory.Exists(basePath + @"\excel\demo"))//判断文件夹是否已经存在
                {
                    Directory.CreateDirectory(basePath + @"\excel\demo");//创建文件夹
                }
                System.IO.FileStream fs = new System.IO.FileStream(basePath + @"\excel\demo\demo.xls", FileMode.OpenOrCreate);
                fs.SetLength(0);
                fs.Write(Properties.Resources.demo, 0, Properties.Resources.demo.Length);
                fs.Close();
                fs.Dispose();                 
            }
            catch { }

            try
            {
                tns_path = System.IO.File.ReadAllText(config_path);
                dtBind = LoadTNSInfo();
                comboBox1.DisplayMember = "NAME";
                comboBox1.ValueMember = "ROWID";
                comboBox1.DataSource = dtBind;
            }
            catch
            {
                MessageBox.Show("请配置TNS文件路径");
            }
        }

        /// <summary>
        /// 连接数据库操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void BTConnect_Click(object sender, EventArgs e)
        {
            string name = TBName.Text.Trim();
            string password = TBPassword.Text.Trim();
            string ip = TBIp.Text.Trim();
            string port = TBPort.Text.Trim();
            string servername = TBServerName.Text.Trim();

            //配置连接串
            connectOracle = @"Provider=OraOLEDB.Oracle.1;
                            Server=DEDICATED; 
                            Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + @")(PORT = " + port + @")))(CONNECT_DATA =(SERVICE_NAME = " + servername + @")));
                            User ID=" + name + @";Password=" + password + @";";
            //连接数据库，初始化所有表信息
            if (TBServerName.Text.Length != 0)
            {
                InitTreeTables();
            }       
        }

        /// <summary>
        /// 初始化左侧 TreeView，绑定所有表
        /// </summary>
        public void InitTreeTables()
        {
            //获取该用户的所有表的表名
            try
            {
                string cmd = @"select TABLE_NAME from user_tab_comments utc where utc.table_type='TABLE'";
                OleDbConnection conn = new OleDbConnection(connectOracle);
                conn.Open();
                OleDbCommand comm = new OleDbCommand(cmd, conn);
                OleDbDataReader getTables = comm.ExecuteReader();

                treeTables.Nodes.Clear();
                TreeNode RootNode = new TreeNode();
                RootNode.Name = TBServerName.Text.Trim();
                RootNode.Text = TBServerName.Text.Trim();
                treeTables.Nodes.Add(RootNode);

                while (getTables.Read())
                {
                    TreeNode Node = new TreeNode();
                    Node.Text = getTables.GetString(0);
                    Node.Name = getTables.GetString(0);
                    RootNode.Nodes.Add(Node);
                }
                treeTables.ExpandAll();
            }
            catch
            {
                MessageBox.Show("初始化[ " + TBServerName.Text + " ]数据库连接出错！");
            }
        }

        //保存当前选择节点
        private void treeTables_AfterSelect(object sender, TreeViewEventArgs e)
        {
            viewTable = treeTables.SelectedNode.Name;
            TBOracleName.Text = TBServerName.Text.ToLower();
            RefreshMap();
        }


        #region 处理TNS文件
        /// <summary>
        /// 初始化TNS，拷贝TNS副本到当前目录，每次启动加载当前目录TNS。修改TNS需要重新配置TNS
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BTTns_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog1 = new OpenFileDialog();
            fileDialog1.InitialDirectory = "d://";
            fileDialog1.Filter = "ora files (*.ora)|*.ora|All files (*.*)|*.*";
            fileDialog1.FilterIndex = 1;
            fileDialog1.RestoreDirectory = true;
            if (fileDialog1.ShowDialog() == DialogResult.OK)
            {
                tns_path = fileDialog1.FileName;
                System.IO.File.WriteAllText(config_path, tns_path);
            }
            else
            {
                tns_path = "";
            }
            try
            {
                dtBind = LoadTNSInfo();
                comboBox1.DisplayMember = "NAME";
                comboBox1.ValueMember = "ROWID";
                comboBox1.DataSource = dtBind;
            }
            catch
            {
                MessageBox.Show("请确认配置文件格式正确！");
            }
        }


        #endregion

        /// <summary>
        /// 重新选择数据库，修改登录信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex != 0)
            {
                TBServerName.Text = dtBind.Rows[comboBox1.SelectedIndex]["SERVERNAME"].ToString();
                TBIp.Text = dtBind.Rows[comboBox1.SelectedIndex]["HOST"].ToString();
                TBPort.Text = dtBind.Rows[comboBox1.SelectedIndex]["PORT"].ToString();
                if (TBServerName.Text.Length != 0)
                {
                    BTConnect_Click(null, null);
                }
            }
        }

        /// <summary>
        /// 读取TNS配置，生成DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable LoadTNSInfo()
        {
            System.IO.StreamReader sr = new StreamReader(tns_path);

            DataTable DTtnsinfo = new DataTable();
            DTtnsinfo.Columns.Add("name", typeof(string));
            DTtnsinfo.Columns.Add("host", typeof(string));
            DTtnsinfo.Columns.Add("port", typeof(string));
            DTtnsinfo.Columns.Add("servername", typeof(string));
            DTtnsinfo.Columns.Add("rowid", typeof(int));
            DTtnsinfo.Rows.Add(DTtnsinfo.NewRow());
            string strContent = sr.ReadToEnd();
            sr.Close();
            #region 采用正则表达式进行匹配 TNS 名称
            Regex Desc = new Regex(@"\s*(\w+\.)*\w+\s*=\s*\(\s*description\s*=", RegexOptions.IgnoreCase | RegexOptions.Compiled);
            Match m;
            for (m = Desc.Match(strContent); m.Success; m = m.NextMatch())
            {
                foreach (Group g in m.Groups)
                {
                    if (g.Length != 0 && (g.Index > 0 && (strContent[g.Index] == '\n' || strContent[g.Index] == '\r') || g.Index == 0)) //this.ColorString( startIndex + g.Index , g.Length , Color.Red )
                    {
                        string tnsName = strContent.Substring(g.Index, g.Length);
                        if (tnsName[tnsName.Length - 1] != '.')
                        {
                            int j = tnsName.IndexOf("=");
                            tnsName = tnsName.Substring(0, j);
                            tnsName = tnsName.Trim();
                            DataRow dr = DTtnsinfo.NewRow();
                            dr["name"] = tnsName;
                            DTtnsinfo.Rows.Add(dr);
                        }
                    }
                }
            }
            #endregion

            #region 遍历字符串，查找对应的节点放到datatable中
            tnsText = File.ReadAllText(tns_path);
            int indexHost = 0;
            int indexPort = 0;
            int indexServerName = 0;
            for (int k = 1; k < DTtnsinfo.Rows.Count; k++)
            {
                string host = "";
                string port = "";
                string servername = "";
                indexHost = tnsText.IndexOf("HOST = ", indexHost) + 7;
                indexPort = tnsText.IndexOf("PORT = ", indexHost) + 7;
                indexServerName = tnsText.IndexOf("SERVICE_NAME = ", indexHost) + 15;
                for (int i = indexHost; i < tnsText.Length; i++)
                {
                    if (tnsText[i] != ')')
                    {
                        host += tnsText[i];
                    }
                    else
                    {
                        break;
                    }
                }

                for (int i = indexPort; i < tnsText.Length; i++)
                {
                    if (tnsText[i] != ')')
                    {
                        port += tnsText[i];
                    }
                    else
                    {
                        break;
                    }
                }

                for (int i = indexServerName; i < tnsText.Length; i++)
                {
                    if (tnsText[i] != ')')
                    {
                        servername += tnsText[i];
                    }
                    else
                    {
                        break;
                    }
                }
                if (k != DTtnsinfo.Rows.Count - 1 && (host == "" || servername == "" || port == ""))
                {
                    MessageBox.Show("配置文件格式不正确！");
                    break;
                }
                else
                {
                    DTtnsinfo.Rows[k]["host"] = host.Trim();
                    DTtnsinfo.Rows[k]["port"] = port.Trim();
                    DTtnsinfo.Rows[k]["servername"] = servername.Trim();
                    DTtnsinfo.Rows[k]["rowid"] = k;
                }
            }
            #endregion
            return DTtnsinfo;
        }

        public void InitTable2Datatable(string TableName)
        {
            dtTable.Clear();
            dtTable.Columns.Clear();

            dtTableP.Clear();
            dtTableP.Columns.Clear();

            dtTableInfo.Clear();
            dtTableInfo.Columns.Clear();

            //列名，类型，长度，注释
            dtTable.Columns.Add("col_name", typeof(string));
            dtTable.Columns.Add("type", typeof(string));
            dtTable.Columns.Add("length", typeof(int));
            dtTable.Columns.Add("comment", typeof(string));

            //存储当前选中的表名和该表的表注释
            dtTableInfo.Columns.Add("TABLE_NAME");
            dtTableInfo.Columns.Add("TABLE_COMMENTS");

            //表名，主键列名
            dtTableP.Columns.Add("TABLE_NAME");
            dtTableP.Columns.Add("COLUMN_NAME");

            string cmd = @"SELECT USER_TAB_COLS.TABLE_NAME   AS 表名,
                           USER_TAB_COLS.COLUMN_NAME  AS 列名,
                           USER_TAB_COLS.DATA_TYPE    AS 数据类型,
                           USER_TAB_COLS.DATA_LENGTH  AS 长度,
                           USER_COL_COMMENTS.COMMENTS AS 备注,       
                           USER_TAB_COLS.NULLABLE     AS 是否为空,
                           USER_TAB_COLS.COLUMN_ID    AS 列序号
                           FROM USER_TAB_COLS
                           INNER JOIN USER_COL_COMMENTS
                           ON USER_COL_COMMENTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
                           AND USER_COL_COMMENTS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME
                           AND USER_TAB_COLS.TABLE_NAME = '" + TableName + @"'";
            //查找主键
            string cmd_serch_p = @"SELECT CU.TABLE_NAME AS TABLE_NAME,CU.COLUMN_NAME AS COLUMN_NAME FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P' AND AU.TABLE_NAME = '" + TableName + "'";
            string cmd_table_comment2 = @"SELECT TABLE_NAME AS 表名,
                                    TABLE_TYPE AS 类型,
                                    COMMENTS AS 注释 
                                    FROM
                                    USER_TAB_COMMENTS
                                    WHERE
                                    TABLE_NAME = '" + TableName + "'";
            OleDbConnection conn = new OleDbConnection(connectOracle);
            conn.Open();
            OleDbCommand comm = new OleDbCommand(cmd, conn);
            OleDbCommand comm2 = new OleDbCommand(cmd_table_comment2, conn);
            OleDbCommand comm3 = new OleDbCommand(cmd_serch_p, conn);

            OleDbDataReader colReader = comm.ExecuteReader();
            OleDbDataReader colReader2 = comm2.ExecuteReader();
            OleDbDataReader colReader3 = comm3.ExecuteReader();
            while (colReader.Read())
            {
                DataRow dr = dtTable.NewRow();
                dr["col_name"] = colReader.GetString(1);
                dr["type"] = colReader.GetString(2);
                if (!colReader.IsDBNull(3))
                {
                    dr["length"] = Convert.ToInt32(colReader.GetValue(3));
                }
                if (!colReader.IsDBNull(4))
                {
                    dr["comment"] = colReader.GetString(4);
                }
                dtTable.Rows.Add(dr);
            }

            while (colReader2.Read())
            {
                DataRow dr = dtTableInfo.NewRow();
                dr["TABLE_NAME"] = colReader2.GetString(0);
                if (!colReader2.IsDBNull(2))
                {
                    dr["TABLE_COMMENTS"] = colReader2.GetString(2);
                }
                dtTableInfo.Rows.Add(dr);
            }
            while (colReader3.Read())
            {
                DataRow dr = dtTableP.NewRow();
                dr["TABLE_NAME"] = colReader3.GetString(0);
                if (!colReader3.IsDBNull(1))
                {
                    dr["COLUMN_NAME"] = colReader3.GetString(1);
                }
                dtTableP.Rows.Add(dr);
            }
            conn.Close();
        }

        /// <summary>
        /// 生成实体类
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void BTCreatObject_Click(object sender, EventArgs e)
        {
            string PersistenceLayerName = TBPersistenceLayer.Text.Trim();
            string baseObjectName = TBBaseObject.Text.Trim();
            string ObjectName = TBObjectName.Text.Trim();
            string RuleName = TBRuleName.Text.Trim();
            string TableName = "";
            RTBObject.Enabled = true;
            listViewSdd.Visible = false;
            Hashtable ht = new Hashtable();
            if (treeTables.SelectedNode == null)
            {
                MessageBox.Show(this, "请在左侧选择一张数据库表进行实体类生成");
            }
            else
            {
                TableName = treeTables.SelectedNode.Name;
                ht = new Hashtable();
                ht.Add("PersistenceLayerName", PersistenceLayerName);
                ht.Add("baseObjectName", baseObjectName);
                ht.Add("ObjectName", ObjectName);
                ht.Add("RuleName", RuleName);
                ht.Add("TableName", TableName);
            }

            InitTable2Datatable(TableName);

            RTBObject.Text = CreatObject.NewObject(ht, dtTable);
            BTCopy.Enabled = true;
        }

        private void BTCreatDic_Click(object sender, EventArgs e)
        {
            string PersistenceLayerName = TBPersistenceLayer.Text.Trim();
            string baseObjectName = TBBaseObject.Text.Trim();
            string ObjectName = TBObjectName.Text.Trim();
            string RuleName = TBRuleName.Text.Trim();
            string TableName = "";
            string comments = "";
            Hashtable ht = new Hashtable();
            if (treeTables.SelectedNode == null)
            {
                MessageBox.Show(this, "请在左侧选择一张数据库表进行实体类生成");
            }
            else
            {
                TableName = treeTables.SelectedNode.Name;
                ht = new Hashtable();
                ht.Add("PersistenceLayerName", PersistenceLayerName);
                ht.Add("baseObjectName", baseObjectName);
                ht.Add("ObjectName", ObjectName);
                ht.Add("RuleName", RuleName);
                ht.Add("TableName", TableName);
            }
            InitTable2Datatable(TableName);
            richTextBoxDic.Text = CreatDic.NewDic(ht, dtTable, dtTableInfo);
            BTCopy.Enabled = true;
        }

        private void BTCopyDic_Click(object sender, EventArgs e)
        {

            if (richTextBoxDic.Text != "")
            {
                Clipboard.SetDataObject(richTextBoxDic.Text);
                MessageBox.Show("复制成功！");
            }
            else
            {
                MessageBox.Show("请确认文本框中存在内容！");
            }
        }
        private void BTCopyMap_Click(object sender, EventArgs e)
        {
            if (richTextBoxMap.Text != "")
            {
                Clipboard.SetDataObject(richTextBoxMap.Text);
                MessageBox.Show("复制成功！");
            }
            else
            {
                MessageBox.Show("请确认文本框中存在内容！");
            }
        }

        private void BTCopy_Click(object sender, EventArgs e)
        {
            if (RTBObject.Text != "")
            {
                Clipboard.SetDataObject(RTBObject.Text);
                MessageBox.Show("复制成功！");
            }
            else
            {
                MessageBox.Show("请确认文本框中存在内容！");
            }
        }

        private void BTAddTns_Click(object sender, EventArgs e)
        {
            DataTable dtTns = LoadTNSInfo();
            save_host = TBIp.Text.Trim();
            save_port = TBPort.Text.Trim();
            save_name = TBServerName.Text.Trim();
            FormTnsName FormTnsName = new FormTnsName();
            FormTnsName.ShowDialog();
            LoadTNSInfo();
        }

        public void RefreshMap()
        {
            if (treeTables.SelectedNode == null)
            {
                MessageBox.Show(this, "请先在左侧选择一张数据库表");
            }
            else
            {
                if (tabControl1.SelectedIndex == 0)
                {
                    Hashtable ht = new Hashtable();
                    ht.Add("username", TBName.Text.Trim().ToUpper());
                    if (checkBoxOracleName.Checked == true)
                    {
                        ht.Add("oraclename", TBOracleName.Text.Trim());
                    }
                    else
                    {
                        ht.Add("oraclename", TBServerName.Text.ToLower());
                    }
                    if (checkBoxCid.Checked == true)
                    {
                        ht.Add("cid", TBCid.Text.Trim());
                    }
                    else
                    {
                        ht.Add("cid", "cid");
                    }
                    if (radioButton1.Checked)
                    {
                        ht.Add("group1", 1);
                    }
                    else if (radioButton2.Checked)
                    {
                        ht.Add("group1", 2);
                    }
                    else if (radioButton3.Checked)
                    {
                        ht.Add("group1", 3);
                    }

                    if (radioButton4.Checked)
                    {
                        ht.Add("group2", 1);
                    }
                    else if (radioButton5.Checked)
                    {
                        ht.Add("group2", 2);
                    }

                    if (radioButton6.Checked)
                    {
                        ht.Add("group3", 1);
                    }
                    else if (radioButton7.Checked)
                    {
                        ht.Add("group3", 2);
                    }

                    InitTable2Datatable(treeTables.SelectedNode.Name.ToString());
                    richTextBoxMap.Text = CreatMap.NewMap(ht, dtTable, dtTableInfo, dtTableP);
                }
            }
        }

        private void BTRecreate_Click(object sender, EventArgs e)
        {
            RefreshMap();
        }

        #region 修改选项，实时更新
        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton3_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton4_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton5_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton6_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void radioButton7_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void checkBoxCid_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }

        private void checkBoxOracleName_CheckedChanged(object sender, EventArgs e)
        {
            RefreshMap();
        }
        #endregion

        private void BTSdd_Click(object sender, EventArgs e)
        {
            try
            {
                string TableName = "";
                listViewSdd.Visible = true;
                listViewSdd.Clear();
                if (treeTables.SelectedNode == null)
                {
                    MessageBox.Show(this, "请在左侧选择一张数据库表进行实体类生成");
                    return;
                }
                else
                {
                    TableName = treeTables.SelectedNode.Name;
                }
                InitTable2Datatable(TableName);
                RTBObject.Enabled = false;

                DataTable dtResult = CreatSddOracle.NewSddTable(dtTable, TableName);
                listViewSdd.GridLines = true;//表格是否显示网格线
                listViewSdd.FullRowSelect = true;//是否选中整行



                listViewSdd.View = View.Details;//设置显示方式
                listViewSdd.Scrollable = true;//是否自动显示滚动条
                listViewSdd.MultiSelect = false;//是否可以选择多行

                //添加表头（列）
                listViewSdd.Columns.Add("Name", "Name");
                listViewSdd.Columns.Add("Type", "Type");
                listViewSdd.Columns.Add("DefaultValue", "DefaultValue");
                listViewSdd.Columns.Add("PubPrv", "PubPrv");
                listViewSdd.Columns.Add("Get", "Get");
                listViewSdd.Columns.Add("Set", "Set");
                listViewSdd.Columns.Add("intablename", "intablename");

                //添加表格内容
                for (int i = 0; i < dtResult.Rows.Count; i++)
                {
                    ListViewItem item = new ListViewItem();
                    item.SubItems.Clear();

                    item.SubItems[0].Text = dtResult.Rows[i][0].ToString();
                    item.SubItems.Add(dtResult.Rows[i][1].ToString());
                    item.SubItems.Add(dtResult.Rows[i][2].ToString());
                    item.SubItems.Add(dtResult.Rows[i][3].ToString());
                    item.SubItems.Add(dtResult.Rows[i][4].ToString());
                    item.SubItems.Add(dtResult.Rows[i][5].ToString());
                    item.SubItems.Add(dtResult.Rows[i][6].ToString());
                    listViewSdd.Items.Add(item);
                }

                listViewSdd.Columns["Name"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["Type"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["DefaultValue"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["PubPrv"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["Get"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["Set"].Width = -1;//根据内容设置宽度
                listViewSdd.Columns["intablename"].Width = -1;//根据内容设置宽度
                MessageBox.Show("文件已经导出到" + basePath + @"\excel\output\");
            }
            catch {
                MessageBox.Show("生成失败，请尝试重新生成或检查数据库");
            }
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (Directory.Exists(basePath + @"\excel\demo"))//判断文件夹是否已经存在
            {
                Directory.Delete(basePath + @"\excel\demo",true);//删除文件夹
            }
        }
    }
}
